

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PopulateCOIOrganizations]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PopulateCOIOrganizations]
GO

/***************************************************************************
* Name:			PopulateCOIOrganizations
* Purpose:		Insert/Update a COIOrganization
*
* PARAMETERS
* Name				Description					
* -------------			-----------------------------------------------------------

****************************************************************************/
CREATE PROCEDURE [dbo].[PopulateCOIOrganizations] 

AS

BEGIN
	Declare @coiOrgId as integer
 	Declare @name as varchar(1000)
	Declare @errorMsg as varchar(1000)
 	Declare @id1 as integer   
 	Declare @id2 as integer   
 	Declare @id3 as integer   
 	Declare @id4 as integer   
 	Declare @id5 as integer   

	DECLARE organizationCursor CURSOR FOR
	select name, [sponsor id: 1] as id1, [sponsor id: 2] as id2, [sponsor id: 3] as id3,
		 [sponsor id: 4] as id4, [sponsor id: 5] as id5 from mergedresults

	OPEN organizationCursor
    
	-- Perform the first fetch.    
	FETCH NEXT FROM organizationCursor into @name, @id1, @id2, @id3, @id4, @id5

 	-- Check @@FETCH_STATUS to see if there are any more rows to fetch.    
 	WHILE @@FETCH_STATUS = 0    
 	BEGIN  

		INSERT INTO COIOrganization( Name, 
						AdmPersonId,
						Active,
						Reviewed,
						DateCreated
					)
					VALUES
					( @name,
						81134,
						1,
						1,
						GetDate()
					)
		if @@Error <> 0
		BEGIN
			SET @errorMsg = 'Failed to insert record into COIOrganization: ' + @name 
			exec master..xp_logevent 65000, @errorMsg, ERROR
		END

		SELECT @coiOrgId = IDENT_CURRENT('COIOrganization')

		if (@id1 <> null and @id1 <> 0)
		INSERT INTO AdmSponsor_COIOrganization_Map (AdmSponsorId, COIOrganizationId)
			VALUES (@id1, @coiOrgId)
		if @@Error <> 0
		BEGIN
			SET @errorMsg = "Failed to insert record into Map: SponsorId=" + cast(@id1 as varchar(10)) + "; COIOrgId=" + cast(@coiOrgId as varchar(10))
			exec master..xp_logevent 65000, @errorMsg, ERROR
		END

		if (@id2 <> null and @id2 <> 0)
		INSERT INTO AdmSponsor_COIOrganization_Map (AdmSponsorId, COIOrganizationId)
			VALUES (@id2, @coiOrgId)
		if @@Error <> 0
		BEGIN
			SET @errorMsg = "Failed to insert record into Map: SponsorId=" + cast(@id2 as varchar(10)) + "; COIOrgId=" + cast(@coiOrgId as varchar(10))
			exec master..xp_logevent 65000, @errorMsg, ERROR
		END


		if (@id3 <> null and @id3 <> 0)
		INSERT INTO AdmSponsor_COIOrganization_Map (AdmSponsorId, COIOrganizationId)
			VALUES (@id3, @coiOrgId)
		if @@Error <> 0
		BEGIN
			SET @errorMsg = "Failed to insert record into Map: SponsorId=" + cast(@id3 as varchar(10)) + "; COIOrgId=" + cast(@coiOrgId as varchar(10))
			exec master..xp_logevent 65000, @errorMsg, ERROR
		END



		if (@id4 <> null and @id4 <> 0)
		INSERT INTO AdmSponsor_COIOrganization_Map (AdmSponsorId, COIOrganizationId)
			VALUES (@id4, @coiOrgId)
		if @@Error <> 0
		BEGIN
			SET @errorMsg = "Failed to insert record into Map: SponsorId=" + cast(@id4 as varchar(10)) + "; COIOrgId=" + cast(@coiOrgId as varchar(10))
			exec master..xp_logevent 65000, @errorMsg, ERROR
		END

	
		if (@id5 <> null and @id5 <> 0)
		INSERT INTO AdmSponsor_COIOrganization_Map (AdmSponsorId, COIOrganizationId)
			VALUES (@id5, @coiOrgId)
		if @@Error <> 0
		BEGIN
			SET @errorMsg = "Failed to insert record into Map: SponsorId=" + cast(@id5 as varchar(10)) + "; COIOrgId=" + cast(@coiOrgId as varchar(10))
			exec master..xp_logevent 65000, @errorMsg, ERROR
		END

		-- This is executed as long as the previous fetch succeeds.    
   		FETCH NEXT FROM organizationCursor into @name, @id1, @id2, @id3, @id4, @id5
	END    
	CLOSE organizationCursor
	DEALLOCATE organizationCursor
		
	RETURN 
	
	
END

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO







